package com.conversationboard.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.joda.time.DateTime;

import com.conversationboard.config.Configuration;
import com.conversationboard.database.Database;
import com.conversationboard.formatter.DateFormatter;

/**
 * Class to look for sockpuppets for the pending registration page - are there users that use the same password as the pending registrants?
 * 
 * @author Keith Watson
 *
 */

public class ExistingUsers {

	/**
	 * Deduplicate the array - we don't want multiple versions of the same user having registered lots of times to make a mess
	 * of the view.
	 * 
	 * @param input
	 * @return
	 */

	private static String[] deduplicate(String[] input) {

		Set set = new HashSet(Arrays.asList(input));
		String[] output = (String[]) (set.toArray(new String[set.size()]));

		return output;
	}


	/**
	 * Pass in a list of passwords and return a list of identifiers of sockpuppet users.
	 * 
	 * @param passwords
	 * @return
	 * @throws SQLException
	 */

	public static List<String> getExistingUsers(String[] passwords) throws SQLException {

		passwords = deduplicate(passwords);

		List<String> existingUsers = new ArrayList<String>();

		if (passwords.length == 0) {
			return existingUsers;
		}

		/* Dynamically constructed query, because it uses an SQL IN statement */

		String query = "SELECT password, loginid, displayname, originaldisplayname, creationdate FROM users WHERE password IN (";

		for (int i = 0; i < passwords.length; i++) {
			query = query + "?, ";
		}

		/* Remove the final comma and space and add in the last bracket - make sure we only check against users from before the interval */

		query = query.substring(0, query.length() - 2) + ") AND (DATE(creationdate) < DATE_SUB(CURRENT_DATE, INTERVAL 3 DAY)) ORDER BY password";

		Connection connection = null;

		try {

			connection = Database.getConnection();

			PreparedStatement statement = connection.prepareStatement(query);

			for (int i = 0; i < passwords.length; i++) {
				statement.setString((i + 1), passwords[i]);
			}

			statement.execute();

			ResultSet resultSet = statement.getResultSet();

			String previousPassword = "";
			int counter = 0;

			while (resultSet.next()) {

				String loginId = resultSet.getString("loginid");
				String displayName = resultSet.getString("displayname");
				String originalDisplayName = resultSet.getString("originaldisplayname");
				String password = resultSet.getString("password");
				Date creationDate = resultSet.getDate("creationdate");
				
				/* Increment the counter if this is a new sockpuppet */

				if (!password.equals(previousPassword)) {
					counter++;
					previousPassword = password;
				}

				String fullIdentifier = "User " + counter + ". " + NameTruncation.truncateName(displayName, originalDisplayName) + ". Login ID is: <a href='http://" + Configuration.getInstance().getDomainName() + Configuration.getInstance().getRoot() + "/Admin/AdminCloneUsersControllerServlet?userid=" + loginId + "'>" + loginId + " (see full sockpuppet report)</a> registered " + DateFormatter.prettyPrintTime(creationDate, null, true, true);
				
				DateTime creation = new DateTime(creationDate);

				if (creation.plusDays(4).isAfterNow()) {
					fullIdentifier = fullIdentifier + " *** NEWLY REGISTERED USER *** ";
				}

				existingUsers.add(fullIdentifier);
			}

		} finally {
			Database.close(connection);
		}

		return existingUsers;

	}

}
